/*
===========================================================
Author:    Joseph Styons
Create date: 8/12/2012
Description:  Creates the database structures to support a notification system
===========================================================
--*/
if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'USP_NOTIFICATIONTYPECODE_CREATE') begin
  drop procedure USP_NOTIFICATIONTYPECODE_CREATE;
end;
if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'USP_NOTIFICATIONRECIPIENTS') begin
  drop procedure USP_NOTIFICATIONRECIPIENTS;
end;
if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'USP_GETACTIVENOTIFICATIONLIST') begin
  drop procedure USP_GETACTIVENOTIFICATIONLIST;
end;
if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'USP_GETNOTIFICATIONCONFIG') begin
  drop procedure USP_GETNOTIFICATIONCONFIG;
end;
if exists(select * from INFORMATION_SCHEMA.TABLES where table_name = 'notificationlog') begin
  drop table notificationlog;
end;
if exists(select * from INFORMATION_SCHEMA.TABLES where table_name = 'notificationschedule') begin
  drop table notificationschedule;
end;
if exists(select * from INFORMATION_SCHEMA.TABLES where table_name = 'notificationrecipient') begin
  drop table notificationrecipient;
end;
if exists(select * from INFORMATION_SCHEMA.TABLES where table_name = 'notification') begin
  drop table notification;
end;
if exists(select * from INFORMATION_SCHEMA.TABLES where table_name = 'notificationtypecode') begin
  drop table notificationtypecode;
end;
if exists(select * from INFORMATION_SCHEMA.TABLES where table_name = 'notificationconfig') begin
  drop table notificationconfig;
end;
if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'UFN_INITCAPS') begin
  drop function UFN_INITCAPS;
end;
GO

/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Begin: UFN_INITCAPS
Utility function to format strings as initcaps
So TABLENAME becomes Tablename
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
create function dbo.UFN_INITCAPS(@s nvarchar(max))
returns nvarchar(max)
as
begin
  return upper(substring(@s,1,1)) + lower(substring(@s,2,100));
end;
GO
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
End: UFN_INITCAPS
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/

/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Begin: NOTIFICATIONTYPECODE
code table for notification types

Necessary for
1) not sure we need it actually  may delete later
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
create table notificationtypecode
(
  ID uniqueidentifier default newid()
 ,DESCRIPTION nvarchar(200)
 ,ISACTIVE bit not null default 1
 --begin: system fields
 ,DATEADDED datetime default getdate()
 ,DATECHANGED datetime default getdate()
 ,ADDEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 ,CHANGEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 --end: system fields
 ,PRIMARY KEY(ID)
);
GO

CREATE procedure dbo.USP_NOTIFICATIONTYPECODE_CREATE
(
  @ID uniqueidentifier = null output
 ,@DESCRIPTION nvarchar(100)
 ,@ASPNETDBUSERID uniqueidentifier = null
)
as
begin
  set nocount on;
  declare @CHANGEAGENTID uniqueidentifier = dbo.UFN_CHANGEAGENT_GET(@ASPNETDBUSERID);
  set @ID = case when (@ID is null) or (@ID = '00000000-0000-0000-0000-000000000000') then NEWID() else @ID end;  
  insert into NOTIFICATIONTYPECODE(ID,DESCRIPTION,ADDEDBYID,CHANGEDBYID)
  values(@ID,@DESCRIPTION,@CHANGEAGENTID,@CHANGEAGENTID);
end;
GO
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
End: NOTIFICATIONTYPECODE
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/

/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Begin: NOTIFICATIONCONFIG
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
create table notificationconfig
(
  ID uniqueidentifier default newid()
 ,FROMEMAILADDRESS nvarchar(100) not null
 ,EMAILSERVERNAME nvarchar(100) not null
 ,EMAILUSERNAME nvarchar(100) not null
 ,EMAILPASSWORD nvarchar(100) not null
 --begin: system fields
 ,DATEADDED datetime default getdate()
 ,DATECHANGED datetime default getdate()
 ,ADDEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 ,CHANGEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 --end: system fields
 ,PRIMARY KEY(ID)
);
GO

create procedure dbo.USP_GETNOTIFICATIONCONFIG
AS
BEGIN
  SELECT TOP 1
    config.EMAILSERVERNAME
   ,config.EMAILPASSWORD
   ,config.EMAILUSERNAME
   ,config.FROMEMAILADDRESS
  FROM
    NOTIFICATIONCONFIG config
  ORDER BY
    DATEADDED DESC;
  SET NOCOUNT ON;
END;
GO
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
End: NOTIFICATIONCONFIG
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/

/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Begin: NOTIFICATION
a table that describes notifications to be sent when certain conditions are met

Necessary for
1) Knowing what condition(s) must be met to send a notification
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
create table notification
(
  ID uniqueidentifier default newid()
 ,TITLE nvarchar(100) not null
 ,FRIENDLYCONDITION nvarchar(max)
 ,NOTIFICATIONTYPECODEID uniqueidentifier not null references notificationtypecode(id)
 ,SQLTABLE nvarchar(max)
 ,SQLWHERE nvarchar(max)
 ,MESSAGE_SUBJECT nvarchar(100)
 ,MESSAGE_BODY nvarchar(max)
 ,ISACTIVE bit not null default 1
 --begin: system fields
 ,DATEADDED datetime default getdate()
 ,DATECHANGED datetime default getdate()
 ,ADDEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 ,CHANGEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 --end: system fields
 ,unique(title)
 ,primary key(id)
);
GO
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
End: NOTIFICATION
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Begin: NOTIFICATION
a table that describes notifications to be sent when certain conditions are met

Necessary for
1) Knowing what condition(s) must be met to send a notification

Note that we support multiple schedules for a notification, but only one set of recipients
(i.e., we do *not* have notificationscheduleid on this table)

we can revisit that later if it becomes an issue (it won't)
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
create table notificationrecipient
(
  ID uniqueidentifier default newid()
 ,NOTIFICATIONID uniqueidentifier references notification(id)
 ,EMAILADDRESS nvarchar(100)
 ,ISACTIVE bit not null default 1
 --begin: system fields
 ,DATEADDED datetime default getdate()
 ,DATECHANGED datetime default getdate()
 ,ADDEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 ,CHANGEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 --end: system fields
 ,unique(EMAILADDRESS)
 ,primary key(id)
);
GO
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
End: NOTIFICATIONRECIPIENT
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Begin: NOTIFICATIONSCHEDULE
a table to hold a list of notification schedules

Necessary for:
1) Knowing how long to wait between notifications

Note that you can have >1 schedule for the same notification
That would mean you could (for example) run a notification every hour *and* every day
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
create table notificationschedule
(
  ID uniqueidentifier default newid()
 ,NOTIFICATIONID uniqueidentifier references notification(id)
 ,TITLE nvarchar(100) not null
 ,INTERVAL decimal not null default 24  --# of hours between sends
 ,ISACTIVE bit not null default 1
 --begin: system fields
 ,DATEADDED datetime default getdate()
 ,DATECHANGED datetime default getdate()
 ,ADDEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 ,CHANGEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 --end: system fields
 ,unique(title)
 ,primary key(id)
);
GO
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
End: NOTIFICATIONSCHEDULE
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/

/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Begin: notificationlog
a table to hold a record of our notification activities

Necessary for:
1) error diagnosis
2) supporting app features like "notify on new constituents"
   b/c we need to know "new since when?"
3) Supporting notifications at interval
   (how long since my last notification?)
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
CREATE TABLE notificationlog
(
  ID UNIQUEIDENTIFIER default newid()
 ,NOTIFICATIONSCHEDULEID uniqueidentifier not null references notificationschedule(id)
 ,NOTIFICATIONID uniqueidentifier not null references notification(id)
 ,ERRORMESSAGE nvarchar(max)
 --begin: system fields
 ,DATEADDED datetime default getdate()
 ,DATECHANGED datetime default getdate()
 ,ADDEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 ,CHANGEDBYID uniqueidentifier references appuser(id) default '1A49F55E-8850-48DD-8C30-C33830DADE2E'
 --end: system fields
 ,primary key(id)
);
GO
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
End: notificationlog
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/

/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Begin: USP_GETACTIVENOTIFICATIONLIST
Just spits out a list of active notifications & schedules
It's expected that the notifier application will be calling this sproc at regular intervals
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
create procedure dbo.USP_GETACTIVENOTIFICATIONLIST
AS
BEGIN
  SET NOCOUNT ON;
  select
    noti.ID as notificationid
   ,sched.ID as notificationscheduleid
  from
    notification noti
    inner join
    notificationschedule sched
    on noti.ID = sched.NOTIFICATIONID
  where noti.ISACTIVE = 1
    and sched.isactive = 1;
END;
GO
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
End: USP_GETACTIVENOTIFICATIONLIST
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/

/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Begin: USP_NOTIFICATIONRECIPIENTS
Takes a notification and schedule id, returns list of folks who should receive a notification right now
If its not time to notify, or if the notification condition has not been met, then return nothing

It's expected that the notifier application will be calling this sproc at regular intervals
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/
create procedure dbo.USP_NOTIFICATIONRECIPIENTS
(
  @notificationid uniqueidentifier
 ,@notificationscheduleid uniqueidentifier
)
as
begin
  set nocount on;

  declare @mostrecentrundate datetime = null;
  select top 1
   @mostrecentrundate = DATEADDED
  from
    notificationlog nlog
  where nlog.NOTIFICATIONID = @notificationid
    and nlog.NOTIFICATIONSCHEDULEID = @notificationscheduleid
  order by
    nlog.DATEADDED desc;

  declare @duetorun bit = 0;

  select
    @duetorun =
      case when GETDATE() >= (@mostrecentrundate + (sched.INTERVAL / 24))
           then 1
           else 0
           end
  from notificationschedule sched
  where sched.ID = @notificationscheduleid;

  if 1=@duetorun begin
    print 'Its time to run the notification process again';
    declare @sqltable nvarchar(max) = null;
    declare @sqlwhere nvarchar(max) = null;
    declare @messagesubject nvarchar(max) = null;
    declare @messagebody nvarchar(max) = null;

    select
      @sqltable = 'SQL2008R2_886711_sep.dbo.' + SQLTABLE
     ,@sqlwhere = SQLWHERE
     ,@messagebody = noti.MESSAGE_BODY
     ,@messagesubject = noti.MESSAGE_SUBJECT
    from
      notification noti
      inner join
      notificationschedule sched
      on noti.ID = sched.NOTIFICATIONID  
    where noti.ID = @notificationid
      and sched.ID = @notificationscheduleid;

    declare @notify bit = 0;
    declare @resultcontainer table(result nvarchar(max));
    declare @parmdefinition nvarchar(max) = '@needtonotify bit OUTPUT';
    declare @sqltest nvarchar(max) = '
    select
      @needtonotify =
        case when exists(
                        select *
                        from ' + @sqltable + '
                        WHERE 1=1
                          AND ' + @sqlwhere + '
                        )
             THEN 1
             ELSE 0
             END';
    EXECUTE sp_executesql @sqltest, @parmdefinition, @needtonotify=@notify output;

    if @notify = 1 begin
      select
        nrec.EMAILADDRESS
       ,@messagesubject AS MESSAGE_SUBJECT
       ,@messagebody AS MESSAGE_BODY
      from 
        notificationrecipient nrec
      where nrec.NOTIFICATIONID = @notificationid
    end
    else begin
      print 'No need to notify anyone';
    end;
  end
  else begin
    print 'Its not time to run this notification yet';
  end;
end;
GO
/*
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
End: USP_NOTIFICATIONRECIPIENTS
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--*/

/*
Enter some dummy data and run the sproc to test this puppy out
--*/
declare @notificationtypecodeid uniqueidentifier = null;
exec USP_NOTIFICATIONTYPECODE_CREATE @notificationtypecodeid output,'Email'

declare @notificationid uniqueidentifier = newid();
insert into notification(ID,TITLE,FRIENDLYCONDITION,NOTIFICATIONTYPECODEID,SQLTABLE,SQLWHERE,MESSAGE_SUBJECT,MESSAGE_BODY)VALUES(@notificationid,'Organizations exist','Constituent table has at least one record in which ISORGANIZATION=1',@notificationtypecodeid,'CONSTITUENT','((ISORGANIZATION = 1) and (ISACTIVE = 1) and (1=1))','Test notification','This is a test notification');

insert into notificationrecipient(NOTIFICATIONID,EMAILADDRESS) values (@notificationid,'joseph.styons@blackbaud.com');
insert into notificationrecipient(NOTIFICATIONID,EMAILADDRESS) values (@notificationid,'jstyons@gmail.com');

declare @notificationscheduleid uniqueidentifier = newid();
insert into notificationschedule(ID,NOTIFICATIONID,TITLE,INTERVAL) values (@notificationscheduleid,@notificationid,'Every hour',1);

declare @pretend_we_last_ran_on datetime = getdate()-1;
insert into notificationlog(NOTIFICATIONSCHEDULEID,NOTIFICATIONID,DATEADDED) values (@notificationscheduleid,@notificationid,@pretend_we_last_ran_on);

--get the list of all active notifications
declare @notificationtest table(notificationid uniqueidentifier, notificationscheduleid uniqueidentifier);
insert into @notificationtest
exec dbo.USP_GETACTIVENOTIFICATIONLIST

--run each and every notification
while(0<(select COUNT(*) from @notificationtest)) begin
  declare @current_notificationid uniqueidentifier = null;
  declare @current_notifictionscheduleid uniqueidentifier = null;
  select top 1
    @current_notificationid = notificationid
   ,@current_notifictionscheduleid = notificationscheduleid
  from @notificationtest

  exec dbo.USP_NOTIFICATIONRECIPIENTS @notificationid,@notificationscheduleid;

  delete @notificationtest where notificationid = @current_notificationid and notificationscheduleid = @current_notifictionscheduleid;
end;

select
  n.TITLE as "Title"
 ,n.FRIENDLYCONDITION as "Condition"
 ,dbo.ufn_initcaps(n.SQLTABLE) as "Base table"
 ,nt.DESCRIPTION  as "Notification type"
 ,nsch.TITLE as "Interval"
from
  notification n
  inner join
  notificationtypecode nt
  on nt.id = n.NOTIFICATIONTYPECODEID
  inner join
  notificationschedule nsch
  on n.id = nsch.NOTIFICATIONID